<?php
/*

  Template Name: Visualization Water

 */

get_template_part('secondary', 'header');
get_template_part('secondary', 'sidebar');

$type_id = 1; //agua

$current_user = wp_get_current_user();
$user_id = $current_user->ID;

$year = date('Y');
$month = date('m');

if($month == 1){
    $year = $year - 1;
    $month = 12;
} else {
    $month = $month - 1;
}

$previous_year = $year - 1;

$consumption_registry_query = $wpdb->get_results($wpdb->prepare("select * from bpa_registry_consumption where user_id = %d and type_id = %d and ((year = %d and month <= %d) or year = %d) order by year desc, month desc limit 12", $user_id, $type_id, $year, $month, $previous_year));
$consumption_registry = array_reverse($consumption_registry_query, true);

$baseline_query = $wpdb->get_results($wpdb->prepare(
        "select c.user_id, c.type_id, c.year, c.month, w.number, b.base 
        from bpa_registry_consumption c
        left join bpa_registry_workers w
        on c.user_id = w.user_id
        and c.year = w.year
        and c.month = w.month
        left join bpa_registry_baseline b
        on c.type_id = b.type_id
        and c.year = b.year
        and c.month = b.month
        where c.user_id = %d
        and c.type_id = %d -- agua
        and ((c.year = %d and c.month <= %d) or c.year = %d)	
        order by c.year desc, c.month desc
        limit 12", $user_id, $type_id, $year, $month, $previous_year));
$baseline = array_reverse($baseline_query, true);

$filter = $_REQUEST['filter'];
if(!isset($filter)){
    $filter="volume";
}

?>

<div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">

    <div id="dash-content" class="visualiza-content">

        <?php if (have_posts()) : ?>

            <?php while (have_posts()) : the_post(); ?>

                <header>
                    <h2><?php the_title(); ?></h2>
                    <h5>&nbsp;</h5>
                </header> 
        
                <div class="filter">
                    <form id="filter_form">
                       Ver consumo por: 
                        <select id="filter_select" name="filter">
                            <option value="volume" <?php if($filter=='volume') echo 'selected' ?> >m&sup3;</option>
                            <option value="price" <?php if($filter=='price') echo 'selected' ?>>Soles</option>
                        </select> 
                    </form>
                </div>

                <div class="v-canvas">
                    <div class="canvas-area">
                        <canvas id="detailed_water_chart" height="500"></canvas>
                    </div>
                    <div id="legend" width="766" height="100"></div>
                </div>

                <div class="visualiza">

                    <div class="v-left">
                        <h3><?php the_title(); ?></h3>
                        <p><?php the_content(); ?></p>
                        <a class="buton agregar" href="<?php echo Helpers::get_user_register_url(); ?>">Agregar nuevo consumo</a>
                    </div>

                    <div class="v-right">

                        <table>
                            <thead>
                                <tr>
                                    <th>Mes</th>
                                    <th>Consumo m&sup3;</th>
                                    <th>Soles</th>
                                </tr>
                            </thead>

                            <tbody>
                            <?php 
                                    $total_consumption=0;
                                    $total_price=0;
                                    foreach ($consumption_registry_query as $registry) { ?>
                                        <tr>
                                            <td><?php echo get_year_from_index($registry->month) . " " . $registry->year; ?></td>
                                            <td class="v-cantidad"><?php echo $registry->quantity; ?></td>
                                            <td class="v-precio">S/.<?php echo sprintf("%.2f", $registry->price); ?></td>
                                        </tr> 
                            <?php       $total_consumption += $registry->quantity;
                                        $total_price += $registry->price;
                                    } ?>
                                <tr>
                                    <th>Total</th>
                                    <th class="v-cantidad"><?php echo $total_consumption; ?> m&sup3;</th>
                                    <th class="v-precio">S/. <?php echo sprintf("%.2f", $total_price); ?></th>
                                </tr>
                            </tbody>
                        </table>

                    </div>

                </div>

            <?php endwhile; ?>

        <?php endif; ?>

    </div>

</div>

<?php

//first line - company values
$company_data = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
$i = 0;
foreach ($consumption_registry as $registry) {
    if($filter=="volume"){
        $company_data[$i] = sprintf("%.2f", $registry->quantity);
    }else{
        $company_data[$i] = sprintf("%.2f", $registry->price);
    }
    $i++;
}

//second line - average values
$average_data = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

$company_sectors_query = $wpdb->get_results($wpdb->prepare(
        "select * from bpa_registry_sector where user_id = %d 
         and ((year = %d and month <= %d) or year = %d) order by year desc, month desc limit 12", 
        $user_id, $year, $month, $previous_year));
$company_sectors = array_reverse($company_sectors_query, true);

$i = 0;
foreach ($company_sectors as $sector) {
    
    $sector_id = $sector->sector_id;
    $year = $sector->year;
    $month = $sector->month;
        
    $average_query = 
        "select ifnull(w.number * (
        select sum(".($filter=="volume" ? "c.quantity" : "c.price")."/ w.number) / count(*) as consumption_per_capita
        from bpa_registry_consumption c
        left join bpa_registry_sector s
        on c.user_id = s.user_id
        and c.year = s.year
        and c.month = s.month
        left join bpa_registry_workers w
        on c.user_id = w.user_id
        and c.year = w.year
        and c.month = w.month
        where c.type_id = %d
        and c.year = %d
        and c.month = %d
        and s.sector_id = %d
        and ".($filter=="volume" ? "c.quantity" : "c.price")." > 0
        order by c.year desc, c.month desc), 0) as average
        from bpa_registry_workers w
        where w.user_id = %d
        and w.year = %d
        and w.month = %d";
    $average_data[$i] = $wpdb->get_var($wpdb->prepare($average_query, $type_id, $year, $month, $sector_id, $user_id, $year, $month));
    $average_data[$i] = sprintf("%.2f", $average_data[$i]);
    $i++;
}

//third line - base line
//  número de días del mes  x  Base  x número de colaboradores
//$_31 =  31*$consumo_m3*$cantidad_colaboradores;
//$_30 =  30*$consumo_m3*$cantidad_colaboradores;
//$_28 =  28*$consumo_m3*$cantidad_colaboradores;
//$base_data = array($_31, $_28, $_31, $_30, $_31, $_30, $_31, $_31, $_30, $_31, $_30, $_31);

$base_data = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

$i = 0;
foreach ($baseline as $field) {
    $base_data[$i] = ($field->base == null ? 0 : $field->base) * ($field->number == null ? 0 : $field->number);
    $i++;
}

//labels of axis x
$labels = get_graph_label_months($consumption_registry);
$sup = "<sup>3</sup>";
$unit_label = '';
if($filter=="volume"){
    $unit_label = '<%= value %> m³' ;
}else{
    $unit_label = 'S/. <%= value %>';
}

?>

<script>

    var water_ctx = document.getElementById("detailed_water_chart").getContext("2d");

    var water_data = {
        labels: ['',<?php echo "'".implode($labels, "','")."'"; ?>],
        datasets: [
            {
                label: "Consumo mensual de la empresa",
                fillColor: "rgba(220,220,220,0.2)",
                strokeColor: "rgba(220,220,220,1)",
                pointColor: "#fff",
                pointStrokeColor: "#fff",
                lineColor: "#fff",
                pointHighlightFill: "#fff",
                pointHighlightStroke: "rgba(220,220,220,1)",
                data: [0, <?php echo implode($company_data, ","); ?>]
            }          
            <?php if($filter=="volume"){  
                    echo ','."\r\n";
                    echo '{'."\r\n";
                    echo  '   label: "Consumo promedio del rubro (seg&uacute;n data registrada en ECOPYME)",'."\r\n";
                    echo  '   fillColor: "rgba(220,220,220,0.2)",'."\r\n";
                    echo  '   strokeColor: "rgba(220,220,220,1)",'."\r\n";
                    echo  '   pointColor: "#0f0",'."\r\n";
                    echo  '   pointStrokeColor: "#0f0",'."\r\n";
                    echo  '   lineColor: "#0f0",'."\r\n";
                    echo  '   pointHighlightFill: "#0f0",'."\r\n";
                    echo  '   pointHighlightStroke: "rgba(220,220,220,1)",'."\r\n";
                    echo  '   data: [0,'.implode($average_data, ",").']'."\r\n";
                    echo '}, ';
                    echo '{'."\r\n";
                    echo  '   label: "Consumo óptimo",'."\r\n";
                    echo  '   fillColor: "rgba(220,220,220,0.2)",'."\r\n";
                    echo  '   strokeColor: "rgba(220,220,220,1)",'."\r\n";
                    echo  '   pointColor: "#000",'."\r\n";
                    echo  '   pointStrokeColor: "#000",'."\r\n";
                    echo  '   lineColor: "#000",'."\r\n";
                    echo  '   pointHighlightFill: "#000",'."\r\n";
                    echo  '   pointHighlightStroke: "rgba(220,220,220,1)",'."\r\n";
                    echo  '   data: [0,'.implode($base_data, ",").']'."\r\n";
                    echo '} ';
                }
            ?>
        ]
    };

    var options = {
        scaleShowGridLines : true,
        scaleFontColor: "rgba(0,0,0,.05)",
        scaleFontColor: "rgba(255,255,255,1)",
        scaleGridLineWidth : 1,
        bezierCurve : false,
        scaleLabel : "<?php echo $unit_label; ?>", 
        bezierCurveTension : 0.4,
        pointDot : true,
        pointDotRadius : 5,
        pointDotStrokeWidth : 2,
        pointHitDetectionRadius : 20,
        datasetStroke : true,
        datasetStrokeWidth : 2,
        responsive: true,
        maintainAspectRatio: false,
        multiTooltipTemplate: "<?php echo $unit_label; ?>",
        datasetFill : true,
        legendTemplate : "<ul class=\"<%=name.toLowerCase()%>-legend\"><% for (var i=0; i<datasets.length; i++){%><li><span style=\"background-color:<%=datasets[i].pointColor%>\"></span><%if(datasets[i].label){%><%=datasets[i].label%><%}%></li><%}%></ul>"
    };

    var myLineChart = new Chart(water_ctx).Line(water_data, options);
    jQuery("#legend").html(myLineChart.generateLegend());

</script>

<?php get_template_part('secondary', 'footer'); ?>